package com.east.study.database;

import com.east.study.EastStudy;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class EastStudyDatabase extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "eaststudy.db";
    private static final int DATABASE_VERSION = 1;

    public EastStudyDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public final Cursor query(String table, String[] columns, String where, String[] whereArgs) {
        return getReadableDatabase().query(table, columns, where, whereArgs, null, null, EastStudy.BaseColumns.DEFAULT_SORT_ORDER);
    }

    public final Cursor rawQuery(String sql, String[] selectionArgs) {
        return getReadableDatabase().rawQuery(sql, selectionArgs);
    }

    public final boolean existsDownloaded(long id) {
        Cursor cursor = getReadableDatabase().query(EastStudy.Downloaded.TABLE_NAME, null, EastStudy.Downloaded.COLUMN_ID + '=' + id, null, null, null, null);
        boolean exists = cursor.moveToFirst();
        cursor.close();

        return exists;
    }

    public final void insertDownloaded(long id) {
        ContentValues values = new ContentValues();
        values.put(EastStudy.Downloaded.COLUMN_ID, id);

        getWritableDatabase().replace(EastStudy.Downloaded.TABLE_NAME, null, values);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_GRADE_TABLE);
        db.execSQL(CREATE_SUBJECT_TABLE);
        //db.execSQL(CREATE_VERSION_TABLE);
        db.execSQL(CREATE_JOIN_TABLE);
        db.execSQL(CREATE_COURSE_TABLE);
        db.execSQL(CREATE_LESSON_TABLE);
        db.execSQL(CREATE_DOWNLOADED_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Join.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Grade.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Lesson.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Course.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Subject.TABLE_NAME);
        //db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Version.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + EastStudy.Downloaded.TABLE_NAME);
        onCreate(db);
    }

    private static final String CREATE_GRADE_TABLE = "CREATE TABLE grade (_id INTEGER PRIMARY KEY,"+
        "name TEXT,sort_order INTEGER);";

    private static final String CREATE_SUBJECT_TABLE = "CREATE TABLE subject (_id INTEGER PRIMARY KEY,"+
        "name TEXT,sort_order INTEGER);";

//    private static final String CREATE_VERSION_TABLE = "CREATE TABLE version (_id INTEGER PRIMARY KEY,"+
//        "name TEXT,sort_order INTEGER);";

//    private static final String CREATE_JOIN_TABLE = "CREATE TABLE join_table (_id INTEGER PRIMARY KEY,"+
//        "grade_id INTEGER,subject_id INTEGER,version_id INTEGER);";

    private static final String CREATE_JOIN_TABLE = "CREATE TABLE join_table (_id INTEGER PRIMARY KEY,"+
        "grade_id INTEGER,subject_id INTEGER);";

    private static final String CREATE_COURSE_TABLE = "CREATE TABLE course (_id INTEGER PRIMARY KEY,"+
        "name TEXT,sort_order INTEGER,join_id INTEGER);";

    private static final String CREATE_LESSON_TABLE = "CREATE TABLE lesson (_id INTEGER PRIMARY KEY,"+
        "name TEXT,sort_order INTEGER,url TEXT,type INTEGER,status INTEGER,course_id INTEGER);";

    private static final String CREATE_DOWNLOADED_TABLE = "CREATE TABLE downloaded (_id INTEGER PRIMARY KEY);";
}